﻿using KdbndpTypes;
using NetTaste;
using NPOI.OpenXmlFormats;
using NPOI.SS.Formula.Functions;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Yuebon.Commons.Linq;

namespace Yuebon.Core.DynamicDb
{
    public class DynamicDbClient
    {
        private string _connection;
        private int _dbType;
        public DynamicDbClient(string connection,int dbType)
        {
            _connection = connection;
            _dbType = dbType;
        }

        private ISqlSugarClient _db
        {
            get
            {
                //创建数据库对象 (用法和EF Dappper一样通过new保证线程安全)
                SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = _connection,
                    DbType = (DbType)_dbType,
                    IsAutoCloseConnection = true
                },
                db => {

                    //db.Aop.OnLogExecuting = (sql, pars) =>
                    //{

                    //    //获取原生SQL推荐 5.1.4.63  性能OK
                    //    Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));

                    //    //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
                    //    //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))


                    //};

                    //注意多租户 有几个设置几个
                    //db.GetConnection(i).Aop

                });

                return Db;
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IEnumerable<T> GetData<T>(string sql) where T:class
        {
           
           return _db.Ado.SqlQuery<T>(sql);
        }

       /// <summary>
       /// 无实体查询
       /// </summary>
       /// <param name="sql"></param>
       /// <returns></returns>
        public List<dynamic> GetData(string sql)
        {
            return _db.Ado.SqlQuery<dynamic>(sql).ToList();
        }
        

        /// <summary>
        /// 插入单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Add<T>(dynamic t) where T : class
        {
            return _db.Insertable(t).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool Add<T>(List<T> list) where T : class
        {
            return _db.Insertable(list).ExecuteCommand()>0;
        }

        /// <summary>
        /// 插入未知类型的数据
        /// </summary>
        /// <param name="singleObject"></param>
        /// <returns></returns>
        public bool AddObject(object singleObject,string tableName)
        {
            var result = _db.InsertableByObject(singleObject).AS(tableName).ExecuteCommand();
            return result>0;
        }

        /// <summary>
        /// 删除未知类型的数据
        /// </summary>
        /// <param name="singleObject"></param>
        /// <returns></returns>
        public bool DeleteObject(object singleObject)
        {
            return _db.DeleteableByObject(singleObject).ExecuteCommand() > 0;
        }

        public bool UpdateObject(object singleObject)
        {
            return _db.UpdateableByObject(singleObject).ExecuteCommand() > 0;
        }

        //public bool executeObject(List<dynamic> list,string tableName,string primaryKey,string operaType)
        //{
        //    var type = GetType(tableName);
        //    var toList = new List<Dictionary<string, object>>();           

        //    foreach (var item in list)
        //    {
        //        PrintDynamicProperties(item,toList);
        //    }

        //    var v = _db.DynamicBuilder().CreateObjectByType(type, toList);

        //    try
        //    {
        //        switch (operaType)
        //        {
        //            case "insert":
        //                _db.InsertableByObject(v).ExecuteCommand();
        //                break;
        //            case "update":
        //                _db.UpdateableByObject(v).WhereColumns(new[] {primaryKey}).ExecuteCommand();
        //                break;
        //            case "delete":
        //                _db.DeleteableByObject(v).ExecuteCommand();
        //                break;
        //            case "insertorupdate":
        //                var result = _db.StorageableByObject(v)
        //                    .ToStorage()
        //                    .WhereColumns(new[] {primaryKey})                              
        //                    .ExecuteCommand();
        //                break;
        //        }
        //        return true;
        //    }
        //    catch(Exception ex) {   
        //        return false;
        //    }

        //}

        public bool executeObject(List<dynamic> list, string tableName, string primaryKey)
        {
            try
            {
                foreach (var item in list)
                {
                    string where = $"{primaryKey}=@id";
                    object obj = null;
                    getPrimaryValue(item, primaryKey, out obj);
                    var exists = _db.Queryable<object>().AS(tableName).Where(where, new { id = obj }).Any();
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    PrintDynamicProperties(item, dic);

                    var type = GetType(tableName);
                    var v = _db.DynamicBuilder().CreateObjectByType(type, dic);

                    if (exists)
                    {
                        ExcuteCommendUpdate(item, tableName, primaryKey);
                    }
                    else
                    {                        
                        ExcuteCommendInsert(item, tableName);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }

        }


        public void getPrimaryValue(dynamic obj, string primaryKey, out object value)
        {
            value = null;
            if (obj == null)
            {
                return;
            }

            // 处理ExpandoObject类型
            if (obj is IDictionary<string, object> expandoDict)
            {
                foreach (var property in expandoDict)
                {
                    //dic.Add(property.Key, property.Value);
                    if (property.Key == primaryKey)
                    {
                        value = property.Value;
                    }
                    //Console.WriteLine($"属性名: {property.Key}, 属性值: {property.Value}");
                }

            }
            else
            {
                // 处理其他动态对象（如匿名类型）
                Type type = obj.GetType();
                PropertyInfo[] properties = type.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    try
                    {
                        object v = property.GetValue(obj);
                        if (property.Name == primaryKey)
                        {
                            value = v;
                        }
                        //dic.Add(property.Name, value);
                        //Console.WriteLine($"属性名: {property.Name}, 属性值: {value}");
                    }
                    catch (Exception ex)
                    {
                        //Console.WriteLine($"获取属性 {property.Name} 时出错: {ex.Message}");
                    }
                }
            }
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private void ExcuteCommendInsert(dynamic obj,string tableName)
        {
            if (obj == null)
            {
                return;
            }            
            StringBuilder fieldBuilder = new StringBuilder();
            StringBuilder valueBuilder = new StringBuilder();


            Dictionary<string, object> dic = new Dictionary<string, object>();

            // 处理ExpandoObject类型
            if (obj is IDictionary<string, object> expandoDict)
            {
                foreach (var property in expandoDict)
                {
                    dic.Add(property.Key, property.Value);
                    //Console.WriteLine($"属性名: {property.Key}, 属性值: {property.Value}");
                }

            }
            else
            {
                // 处理其他动态对象（如匿名类型）
                Type type = obj.GetType();
                PropertyInfo[] properties = type.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    try
                    {
                        object value = property.GetValue(obj);
                        dic.Add(property.Name, value);
                        //Console.WriteLine($"属性名: {property.Name}, 属性值: {value}");
                    }
                    catch (Exception ex)
                    {
                        //Console.WriteLine($"获取属性 {property.Name} 时出错: {ex.Message}");
                    }
                }
            }
            int i = 0;
            dic.ForEach(item =>
            {
                if (i == 0)
                {
                    fieldBuilder.Append(item.Key);
                    valueBuilder.Append($"@{item.Key}");
                }
                else
                {
                    fieldBuilder.Append($",{item.Key}");
                    valueBuilder.Append($",@{item.Key}");
                }
                i = i + 1;
            });
            string insertSql = $"insert into {tableName} ({fieldBuilder.ToString()}) values ({valueBuilder.ToString()})";


            _db.Ado.ExecuteCommand(insertSql, dic);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private void ExcuteCommendUpdate(dynamic obj, string tableName,string primaryKey)
        {
            if (obj == null)
            {
                return;
            }
            StringBuilder fieldBuilder = new StringBuilder(); 
            Dictionary<string, object> dic = new Dictionary<string, object>();

            // 处理ExpandoObject类型
            if (obj is IDictionary<string, object> expandoDict)
            {
                foreach (var property in expandoDict)
                {                    
                     dic.Add(property.Key, property.Value);
                }

            }
            else
            {
                // 处理其他动态对象（如匿名类型）
                Type type = obj.GetType();
                PropertyInfo[] properties = type.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    try
                    {
                        object value = property.GetValue(obj); 
                        dic.Add(property.Name, value);
                    }
                    catch (Exception ex)
                    {
                        
                    }
                }
            }
            int i = 0;
            dic.ForEach(item =>
            {
                if (i == 0)
                {
                    fieldBuilder.Append($"{item.Key}=@{item.Key}");
                    
                }
                else
                {
                    fieldBuilder.Append($",{item.Key}=@{item.Key}");
                    
                }
                i = i + 1;
            });
            string updateSql = $"update {tableName} set {fieldBuilder.ToString()} where {primaryKey}=@{primaryKey}";
            _db.Ado.ExecuteCommand(updateSql, dic);
        }



        private void PrintDynamicProperties(dynamic obj, Dictionary<string, object> dic)
        {
            if (obj == null)
            {
                Console.WriteLine("对象为空");
                return;
            }            

            // 处理ExpandoObject类型
            if (obj is IDictionary<string, object> expandoDict)
            {
                foreach (var property in expandoDict)
                {
                    dic.Add(property.Key, property.Value);
                    //Console.WriteLine($"属性名: {property.Key}, 属性值: {property.Value}");
                }

            }
            else
            {
                // 处理其他动态对象（如匿名类型）
                Type type = obj.GetType();
                PropertyInfo[] properties = type.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    try
                    {
                        object value = property.GetValue(obj);
                        dic.Add(property.Name, value);
                        //Console.WriteLine($"属性名: {property.Name}, 属性值: {value}");
                    }
                    catch (Exception ex)
                    {
                        //Console.WriteLine($"获取属性 {property.Name} 时出错: {ex.Message}");
                    }
                }
            }            
        }


        public object queryObject(string tableName)
        {
            var type = GetType();
            return _db.QueryableByObject(type).ToList();
        }

        public Type GetType(string tableName)        {
            var typeBilder = _db.DynamicBuilder().CreateClass(tableName, new SugarTable() { }); 
            var type = typeBilder.BuilderType();
            //_db.CodeFirst.InitTables(tableName);
            return type;
            
        }
       
    }
}
